package io.aicn.lowcode.service.dbmanager.impl;

import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import io.aicn.lowcode.entity.dbmanager.DataSource;
import io.aicn.lowcode.entity.dbmanager.DataTable;
import io.aicn.lowcode.mapper.dbmanager.DataTableMapper;
import io.aicn.lowcode.service.dbmanager.IDataSourceService;
import io.aicn.lowcode.service.dbmanager.IDataTableService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.sql.*;
import java.time.LocalDateTime;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import java.util.ArrayList;
import java.util.List;

/**
 * 数据表信息Service实现类
 * 
 * @author lowcode-generator
 * @since 2025-01-25
 */
@Slf4j
@Service
public class DataTableServiceImpl extends ServiceImpl<DataTableMapper, DataTable> implements IDataTableService {

    @Autowired
    private IDataSourceService dataSourceService;

    @Override
    public List<DataTable> listByDataSourceId(Long dataSourceId) {
        QueryWrapper<DataTable> queryWrapper = new QueryWrapper<>();
        queryWrapper.eq("datasource_id", dataSourceId)
                   .orderByDesc("update_time");
        
        return this.list(queryWrapper);
    }

    @Override
    public List<DataTable> listByDatasourceAndDatabase(Long datasourceId, String databaseName) {
        return baseMapper.selectByDatasourceAndDatabase(datasourceId, databaseName);
    }

    @Override
    public List<DataTable> listByTableNameLike(Long datasourceId, String tableName) {
        return baseMapper.selectByTableNameLike(datasourceId, tableName);
    }

    @Override
    public int countByDatasourceId(Long datasourceId) {
        return baseMapper.countByDatasourceId(datasourceId);
    }

    @Override
    public List<String> listDatabasesByDatasourceId(Long datasourceId) {
        return baseMapper.selectDatabasesByDatasourceId(datasourceId);
    }

    @Override
    public String syncTablesFromDataSource(Long datasourceId, String databaseName) {
        DataSource dataSource = dataSourceService.getById(datasourceId);
        if (dataSource == null) {
            return "数据源不存在";
        }

        try {
            String url = buildJdbcUrl(dataSource, databaseName);
            Class.forName(getDriverClassName(dataSource.getType()));
            
            try (Connection connection = DriverManager.getConnection(url, dataSource.getUsername(), dataSource.getPassword())) {
                List<DataTable> tables = extractTablesFromDatabase(connection, datasourceId, databaseName);
                
                // 删除旧的表信息
                if (databaseName != null) {
                    lambdaUpdate()
                        .eq(DataTable::getDatasourceId, datasourceId)
                        .eq(DataTable::getDatabaseName, databaseName)
                        .remove();
                } else {
                    lambdaUpdate()
                        .eq(DataTable::getDatasourceId, datasourceId)
                        .remove();
                }
                
                // 保存新的表信息
                if (!tables.isEmpty()) {
                    saveBatch(tables);
                }
                
                return String.format("同步完成: 共同步%d张表", tables.size());
            }
        } catch (Exception e) {
            log.error("同步表结构失败: {}", e.getMessage(), e);
            return "同步失败: " + e.getMessage();
        }
    }

    @Override
    public boolean refreshTableStats(Long tableId) {
        DataTable dataTable = this.getById(tableId);
        if (dataTable == null) {
            throw new RuntimeException("数据表不存在: " + tableId);
        }

        DataSource dataSource = dataSourceService.getById(dataTable.getDatasourceId());
        if (dataSource == null) {
            throw new RuntimeException("数据源不存在: " + dataTable.getDatasourceId());
        }

        try {
            String url = buildJdbcUrl(dataSource, dataTable.getDatabaseName());
            Class.forName(getDriverClassName(dataSource.getType()));
            
            try (Connection connection = DriverManager.getConnection(url, dataSource.getUsername(), dataSource.getPassword())) {
                updateTableStats(connection, dataTable);
                this.updateById(dataTable);
                return true;
            }
        } catch (Exception e) {
            log.error("刷新表统计信息失败: {}", e.getMessage(), e);
            return false;
        }
    }
    
    @Override
    public boolean syncTableStructure(Long tableId) {
        try {
            DataTable dataTable = this.getById(tableId);
            if (dataTable == null) {
                throw new RuntimeException("表不存在: " + tableId);
            }
            
            // 获取数据源信息
            DataSource dataSource = dataSourceService.getById(dataTable.getDatasourceId());
            if (dataSource == null) {
                throw new RuntimeException("数据源不存在: " + dataTable.getDatasourceId());
            }
            
            // 构建JDBC连接URL
            String jdbcUrl = String.format("jdbc:mysql://%s:%d/%s?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai",
                    dataSource.getHost(), dataSource.getPort(), dataTable.getDatabaseName());
            
            // 获取数据库连接并同步表结构
            try (Connection connection = DriverManager.getConnection(jdbcUrl, dataSource.getUsername(), dataSource.getPassword())) {
                syncTableStructureFromDatabase(connection, dataTable);
                
                // 更新表的更新时间
                dataTable.setUpdateTime(LocalDateTime.now());
                this.updateById(dataTable);
                
                return true;
            }
        } catch (Exception e) {
            log.error("同步表结构失败: {}", e.getMessage(), e);
            return false;
        }
    }
    
    /**
     * 从数据库同步表结构信息
     */
    private void syncTableStructureFromDatabase(Connection connection, DataTable dataTable) throws SQLException {
        // 查询表注释
        String tableCommentSql = "SELECT TABLE_COMMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ?";
        try (PreparedStatement stmt = connection.prepareStatement(tableCommentSql)) {
            stmt.setString(1, dataTable.getDatabaseName());
            stmt.setString(2, dataTable.getTableName());
            
            try (ResultSet rs = stmt.executeQuery()) {
                if (rs.next()) {
                    String tableComment = rs.getString("TABLE_COMMENT");
                    dataTable.setTableComment(tableComment);
                }
            }
        }
        
        // 这里可以扩展更多表结构同步逻辑，比如字段信息、索引信息等
        log.info("表结构同步完成: {}.{}", dataTable.getDatabaseName(), dataTable.getTableName());
    }

    @Override
    public String refreshAllTableStats(Long datasourceId) {
        List<DataTable> tables = listByDataSourceId(datasourceId);
        int total = tables.size();
        int success = 0;
        
        for (DataTable table : tables) {
            if (refreshTableStats(table.getId())) {
                success++;
            }
        }
        
        return String.format("刷新完成: 总计%d张表，成功%d张，失败%d张", total, success, total - success);
    }

    /**
     * 从数据库中提取表信息
     */
    private List<DataTable> extractTablesFromDatabase(Connection connection, Long datasourceId, String databaseName) throws SQLException {
        List<DataTable> tables = new ArrayList<>();
        DatabaseMetaData metaData = connection.getMetaData();
        
        try (ResultSet rs = metaData.getTables(databaseName, null, null, new String[]{"TABLE"})) {
            while (rs.next()) {
                DataTable table = new DataTable();
                table.setDatasourceId(datasourceId);
                table.setDatabaseName(rs.getString("TABLE_CAT"));
                table.setTableName(rs.getString("TABLE_NAME"));
                table.setTableComment(rs.getString("REMARKS"));
                table.setCreateTime(LocalDateTime.now());
                table.setUpdateTime(LocalDateTime.now());
                
                // 获取表的详细信息
                updateTableStats(connection, table);
                
                tables.add(table);
            }
        }
        
        return tables;
    }

    /**
     * 更新表的统计信息
     */
    private void updateTableStats(Connection connection, DataTable table) {
        try {
            // 获取行数
            String countSql = String.format("SELECT COUNT(*) FROM `%s`", table.getTableName());
            try (PreparedStatement ps = connection.prepareStatement(countSql);
                 ResultSet rs = ps.executeQuery()) {
                if (rs.next()) {
                    table.setRowCount(rs.getLong(1));
                }
            }
            
            // 获取表信息（MySQL特定）
            if (connection.getMetaData().getDatabaseProductName().toLowerCase().contains("mysql")) {
                String infoSql = "SELECT ENGINE, TABLE_COLLATION, DATA_LENGTH FROM information_schema.TABLES WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ?";
                try (PreparedStatement ps = connection.prepareStatement(infoSql)) {
                    ps.setString(1, table.getDatabaseName());
                    ps.setString(2, table.getTableName());
                    try (ResultSet rs = ps.executeQuery()) {
                        if (rs.next()) {
                            table.setEngine(rs.getString("ENGINE"));
                            table.setCollation(rs.getString("TABLE_COLLATION"));
                            table.setDataSize(rs.getLong("DATA_LENGTH"));
                            
                            // 从collation推断charset
                            String collation = table.getCollation();
                            if (collation != null) {
                                if (collation.startsWith("utf8mb4")) {
                                    table.setCharset("utf8mb4");
                                } else if (collation.startsWith("utf8")) {
                                    table.setCharset("utf8");
                                } else if (collation.startsWith("latin1")) {
                                    table.setCharset("latin1");
                                }
                            }
                        }
                    }
                }
            }
            
            table.setUpdateTime(LocalDateTime.now());
            
        } catch (SQLException e) {
            log.warn("获取表统计信息失败: {}", e.getMessage());
        }
    }

    /**
     * 构建JDBC连接URL
     */
    private String buildJdbcUrl(DataSource dataSource, String databaseName) {
        String dbName = databaseName != null ? databaseName : dataSource.getDatabaseName();
        String baseUrl = String.format("jdbc:%s://%s:%d/%s", 
            dataSource.getType(), 
            dataSource.getHost(), 
            dataSource.getPort(), 
            dbName);
        
        // 添加默认参数
        if ("mysql".equals(dataSource.getType())) {
            baseUrl += "?useUnicode=true&characterEncoding=UTF-8&useSSL=true&serverTimezone=UTC";
        }
        
        return baseUrl;
    }

    /**
     * 获取驱动类名
     */
    private String getDriverClassName(String type) {
        switch (type.toLowerCase()) {
            case "mysql":
                return "com.mysql.cj.jdbc.Driver";
            case "postgresql":
                return "org.postgresql.Driver";
            default:
                throw new RuntimeException("不支持的数据库类型: " + type);
        }
    }
}